﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DoeObjects;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using System.Data.SqlServerCe;

namespace DoeLibsTest
{
    public class TestContext
    {
        private static bool initialized = false;

        /// <summary>
        /// reads the connectionstring from the App.config file and sets the ConnectionString property from the Settings class
        /// </summary>
        public static void initialize()
        {
            if (!initialized)
            {
                initializeDataDirectory();
                initializeDatabaseConnectionString();

                initialized = true;
            }
        }

        public static void initializeEmptyDB()
        {
            if (!initialized)
            {
                var directoryName = System.IO.Path.GetDirectoryName(Assembly.GetEntryAssembly().Location);
                var fileName = System.IO.Path.Combine(directoryName, "TemporaryTestDB.sdf");

                /* check if exists */
                if (File.Exists(fileName))
                    File.Delete(fileName);

                string connStr = @"Data Source = " + fileName;

                /* create Database */
                SqlCeEngine engine = new SqlCeEngine(connStr);
                engine.CreateDatabase();

                /* create table and columns */
                using (SqlCeConnection conn = new SqlCeConnection(connStr))
                {
                    using (SqlCeCommand cmd = new SqlCeCommand(@"CREATE TABLE FooTable (Foo_ID int, FooData NVARCHAR(200))", conn))
                    {
                        try
                        {
                            conn.Open();
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            //MessageBox.Show(ex.ToString());
                        }
                        finally
                        {
                            conn.Close();
                        }

                    }
                }




                initializeDatabaseConnectionString();

                initialized = true;
            }
        }

        private static void initializeDataDirectory()
        {
            AppDomain.CurrentDomain.SetData("DataDirectory", System.Configuration.ConfigurationManager.AppSettings["DataDirectory"]);
            
            //Ken's Data Directory. Please just comment out and don't delete
            //AppDomain.CurrentDomain.SetData("DataDirectory", System.Configuration.ConfigurationManager.AppSettings["KenDataDirectory"]);
        }

        private static void initializeDatabaseConnectionString()
        {
            Settings.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DoeLibString"].ConnectionString;
        }

        /// <summary>
        /// deletes all entries from the testdatabase (except the usercategories)
        /// </summary>
        public static void clearDatabase()
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand();
            SqlTransaction transaction = null;

            try
            {
                connection.Open();

                transaction = connection.BeginTransaction();

                command.Connection = connection;
                command.Transaction = transaction;

                //delete all notifications
                command.CommandText = "TRUNCATE TABLE Notification";
                command.ExecuteNonQuery();

                //delete all reservations:
                command.CommandText = "TRUNCATE TABLE Reservation";
                command.ExecuteNonQuery();

                //delete all loans
                command.CommandText = "TRUNCATE TABLE Loan";
                command.ExecuteNonQuery();

                //delete all loanables
                command.CommandText = "TRUNCATE TABLE Loanable";
                command.ExecuteNonQuery();

                //delete all loanableCategories
                command.CommandText = "TRUNCATE TABLE LoanableCategory";
                command.ExecuteNonQuery();

                //delete all title_has_author
                command.CommandText = "TRUNCATE TABLE Title_has_authors";
                command.ExecuteNonQuery();

                //delete all authors
                command.CommandText = "TRUNCATE TABLE Author";
                command.ExecuteNonQuery();

                //delete all titles
                command.CommandText = "TRUNCATE TABLE Title";
                command.ExecuteNonQuery();

                //delete all publisher
                command.CommandText = "TRUNCATE TABLE Publisher";
                command.ExecuteNonQuery();

                //delete all users
                command.CommandText = "TRUNCATE TABLE User";
                command.ExecuteNonQuery();

                //dont delete usercategories


                transaction.Commit();
            }
            catch (SqlException ex)
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }

                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
        }
    }
}
